# -*- coding: utf-8 -*-
"""
Created on Tue Mar 19 11:14:35 2019

@author: STIECHE
"""

# Import `os` 
import os

# Retrieve current working directory (`cwd`)
cwd = os.getcwd()
cwd

# Change directory 
#os.chdir('C:\\users\\Simon\\Dropbox\\EPFR_MS\\data_RAW\\cedif')
os.chdir(r'C:\Users\simon\Dropbox\0.EPFR_paper\other data\equity')

# List all files and directories in current directory
os.listdir('.')

# Import `load_workbook` module from `openpyxl`
from openpyxl import load_workbook

### Equity indices
file = 'daily_equity_indices'

# Load in the workbook
wb = load_workbook(file + '.xlsx')


# Get sheet names
print(wb.sheetnames)

sheetlist = wb.sheetnames

for sheetname in sheetlist:
    print(sheetname)

    # Get a sheet by name 
    sheet = wb[sheetname]

    sheet.insert_rows(6, amount=1)

    # Write to the sheet of the workbook 
    sheet['A6'] = 'date'
    sheet['B6'] = 'usdmsci_' + sheetname
    sheet['C6'] = 'msci_' + sheetname
    #sheet.delete_cols(11, amount=1)

wb.save(file + '_clean.xlsx')